<?php 

找90后女生

select * from stu where sex='女' and date >"1990-01-01";


找年龄最小的

select * from stu order by date desc limit 1;

统计数据

select count(*) from stu where sex ="男";

找最大值
  select max(age),name from stu;

找最小值
  select min(age) from stu;

求和
  select sum(score) from stu;

  求平均

  select sum(score)/count(*) from stu;

  select avg(score) from stu;


  性别进行分组

  select count(*),sex from stu group by sex;

  分数分组

  select count(*),score from stu group by score;

以性别分级求各自的平均分
   select avg(score),sex from stu group by sex;

在结果后进行筛选
   select avg(score) avg,sex from stu group by sex having avg>100;


女的总分
   select sum(score) from stu where sex ="女";

90后喜欢泡妞的总分

   select sum(score) from stu where date>'1990-01-01' and hobby like "%泡妞%";


   找出最高分的那个性别对应的分数

   select sum(score) from stu where sex in (select sex from stu order by score desc limit 1);



   CREATE TABLE `homework` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(10) NOT NULL,
  `date` datetime NOT NULL,
  `score` int(3) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8;



笛卡尔基
   select * from class.stu.homework;

根据条件查询相对应的数据
   select * from class,stu where class.id=stu.cid;


   select class.class_name,stu.name from class,stu where class.id=stu.cid;

   另外一种写法

    select * from stu inner join class on stu.cid=class.id;





    select t1.name,t2.class_name,t3.date,t3.score from stu t1 inner join class t2  on t1.cid=t2.id inner join homework t3 on t1.id=t3.uid where t3.date>'2016-01-21';



左链接表

select * from stu t1 left join homework t2 on t1.id=t2.uid;

右链接表

select * from stu t1 right join homework t2 on t1.id=t2.uid;

找出郑荣佳同班的
select name from stu where cid in (select cid from stu where name='郑荣佳');

自己链接自己



select t2.* from stu t1 inner join stu t2 on t1.cid=t2.cid  where t1.name='郑荣佳';









 ?>